# -*- coding: utf-8 -*-
#   sql语句解析，加where
# @Time    ： 2025/11/19  15:12
# @Auther  ： 徐良
# File     :  sqlParseTool.py
from typing import List, Optional, Union, Dict, Any, Tuple
import re
from baseClass.tools.strFunction import quote
from PySide6.QtCore import QDate, Qt
from PySide6.QtSql import QSqlQueryModel
import sqlparse
from sqlparse.tokens import Keyword, DML
from sqlparse.sql import IdentifierList, Identifier
from sqlparse.sql import Token

# 安全相关辅助函数（需根据实际数据库实现）
def escape_identifier(identifier: str) -> str:
    """转义SQL标识符(表名、字段名)"""
    # 示例实现，不同数据库转义方式不同
    return f'"{identifier}"'  # 大多数SQL数据库用双引号


def escape_string(value: str) -> str:
    """转义字符串值，防止SQL注入"""
    # 示例实现，应替换为数据库特定的转义函数
    return value.replace("'", "''")

class SqlParseTool:
    """
    SqlParse 是解析sql语句的工具类
    """
    @staticmethod
    def escape_string_for_database(s: str) -> str:
        if not isinstance(s, str):
            return s
        return s.replace("'", "''")

    @staticmethod
    def clean_redundant_keywords(sql: str) -> str:
        patterns = {
            r'\bWHERE\s+WHERE\b': 'WHERE',
            r'\bGROUP\s+BY\s+GROUP\s+BY\b': 'GROUP BY',
            r'\bORDER\s+BY\s+ORDER\s+BY\b': 'ORDER BY',
            r'\bHAVING\s+HAVING\b': 'HAVING',
        }
        sql_clean = sql
        changed = True
        while changed:
            changed = False
            for pat, repl in patterns.items():
                new_sql = re.sub(pat, repl, sql_clean, flags=re.I)
                if new_sql != sql_clean:
                    changed = True
                    sql_clean = new_sql
        return sql_clean

    @staticmethod
    def extract_table_aliases(sql: str) -> Dict[str, str]:
        """提取SQL中所有表的别名映射，格式: {表名: 别名}"""
        aliases = {}

        # 只处理FROM到WHERE之间的部分
        from_to_where = re.split(r'\bWHERE\b', sql, flags=re.I)[0]

        # 匹配主表和别名 (FROM table [AS] alias)
        main_table_pattern = re.compile(r'FROM\s+(\w+)(?:\s+AS?\s+(\w+))?', re.I)
        main_match = main_table_pattern.search(from_to_where)
        if main_match:
            table_name = main_match.group(1).lower()
            alias = main_match.group(2)
            if alias:
                aliases[table_name] = alias

        # 匹配JOIN的表和别名 (JOIN table [AS] alias)
        join_pattern = re.compile(r'(?:LEFT|RIGHT|INNER|OUTER|CROSS)\s+JOIN\s+(\w+)(?:\s+AS?\s+(\w+))?', re.I)
        join_matches = join_pattern.findall(from_to_where)

        for table_name, alias in join_matches:
            if alias:
                aliases[table_name.lower()] = alias

        # 尝试从SELECT子句中提取可能的字段所属表
        select_clause = re.split(r'\bFROM\b', from_to_where, flags=re.I)[0]
        field_pattern = re.compile(r'(\w+)\.(\w+)', re.I)
        field_matches = field_pattern.findall(select_clause)

        # 添加从字段引用中发现的别名
        for alias, field in field_matches:
            if alias not in [v for v in aliases.values()]:
                # 无法确定对应的表名，暂时只记录别名
                aliases[f"unknown_table_{alias}"] = alias

        return aliases


    # region 解析sql语句
    @staticmethod
    def _parse_sql_structure(sql: str) -> dict:
        """
        返回 SQL 各主子句内容，保留原始换行和缩进
        支持复杂 SQL（子查询、CASE、多行）
        """
        if not sql or not sql.strip():
            return {}

        # 去掉注释，但保留原始换行和缩进
        formatted_sql = sqlparse.format(sql, strip_comments=True, reindent=False).strip()
        parsed = sqlparse.parse(formatted_sql)
        if not parsed:
            return {}

        stmt = parsed[0]
        sql_text = str(stmt)

        # 定义顶层关键字
        clause_keywords = [
            ("SELECT", "select"),
            ("FROM", "from"),
            ("WHERE", "where"),
            ("GROUP BY", "group_by"),
            ("ORDER BY", "order_by"),
            ("LIMIT", "limit"),
        ]

        # 查找顶层关键字位置（忽略子查询）
        positions = {}
        depth = 0
        i = 0
        length = len(sql_text)
        upper_sql = sql_text.upper()

        while i < length:
            ch = sql_text[i]
            if ch == '(':
                depth += 1
            elif ch == ')':
                depth = max(0, depth - 1)

            if depth == 0:
                for kw, key in clause_keywords:
                    if upper_sql[i:].lstrip().startswith(kw):
                        offset = len(upper_sql[i:]) - len(upper_sql[i:].lstrip())
                        pos = i + offset
                        if key not in positions:  # 只记录第一个顶层关键字位置
                            positions[key] = pos
            i += 1

        if "select" not in positions:
            return {}

        # 按关键字在 SQL 中出现顺序排序
        ordered = sorted(positions.items(), key=lambda x: x[1])
        result = {key: None for _, key in clause_keywords}

        for idx, (key, start) in enumerate(ordered):
            end = len(sql_text)
            if idx + 1 < len(ordered):
                end = ordered[idx + 1][1]
            kw_text = next(k for k, name in clause_keywords if name == key)
            clause_body = sql_text[start + len(kw_text):end].strip()
            result[key] = clause_body if clause_body else None

        return result

    @staticmethod
    def get_swo(sql: str) -> dict:
        """
        修正：从后往前拆分SQL，确保各子句正确分离
        返回键：
        - 's': SELECT+FROM/JOIN（无WHERE/GROUP/HAVING/ORDER）
        - 'w': WHERE子句内容（不含WHERE关键字）
        - 'g': GROUP BY子句（含GROUP BY关键字）
        - 'h': HAVING子句（含HAVING关键字）
        - 'o': ORDER BY子句（含ORDER BY关键字）
        """
        # 标准化SQL：移除多余空格，统一为单空格
        normalized = re.sub(r'\s+', ' ', sql.strip())
        swd = {'s': '', 'w': '', 'g': '', 'h': '', 'o': ''}

        # 1. 拆分ORDER BY（最后一个ORDER BY，避免子查询干扰）
        order_match = re.search(r'\bORDER\s+BY\b', normalized, re.I)
        if order_match:
            swd['o'] = normalized[order_match.start():].strip()  # 含ORDER BY关键字
            normalized = normalized[:order_match.start()].strip()  # 剩余部分继续处理
        else:
            swd['o'] = ''

        # 2. 拆分HAVING
        having_match = re.search(r'\bHAVING\b', normalized, re.I)
        if having_match:
            swd['h'] = normalized[having_match.start():].strip()  # 含HAVING关键字
            normalized = normalized[:having_match.start()].strip()
        else:
            swd['h'] = ''

        # 3. 拆分GROUP BY
        group_match = re.search(r'\bGROUP\s+BY\b', normalized, re.I)
        if group_match:
            swd['g'] = normalized[group_match.start():].strip()  # 含GROUP BY关键字
            normalized = normalized[:group_match.start()].strip()
        else:
            swd['g'] = ''

        # 4. 拆分WHERE
        where_match = re.search(r'\bWHERE\b', normalized, re.I)
        if where_match:
            swd['w'] = normalized[where_match.end():].strip()  # 不含WHERE关键字（仅条件）
            swd['s'] = normalized[:where_match.start()].strip()  # SELECT+FROM/JOIN
        else:
            swd['s'] = normalized  # 无WHERE时，剩余部分即SELECT+FROM/JOIN

        return swd

    @staticmethod
    def get_swo_str(sql: str) -> Tuple[str, str, str]:
        """
        获取sql语句的 select 、where 、order三个部分
        :return:
        """
        p_dict = SqlParseTool._parse_sql_structure(sql)
        select = p_dict['select']
        where = p_dict.get('where', '')
        group = p_dict.get('group_by', '')

        if where is None:
            where = ''

        if group is None:
            group = ''

        return select, where, group

    def get_sql_s_f(self, sql: str) -> Tuple[str, str]:
        """
        获取sql的select部分
        :param sql:
        :return: select, from 的内容
        """
        result = self._parse_sql_structure(sql)
        return result.get('select'), result.get('from')

    def get_sql_s_f_w(self, sql: str) -> Tuple[str, str, str]:
        """
        获取sql的select、from where 部分
        :param sql:
        :return:
        """
        result = self._parse_sql_structure(sql)
        return result.get('select'), result.get('from'), result.get('where')

    @staticmethod
    def get_sql_s_f_w_o(sql: str) -> Tuple[str, str, str, str]:
        """
        获取sql的select、from、where、 order部分
        :param sql:
        :return:
        """
        result = SqlParseTool._parse_sql_structure(sql)
        return result.get('select'), result.get('from'), result.get('where'), result.get('order_by')

    def get_sql_s_w_o_g(self, sql: str) -> Tuple[str, str, str, str]:
        """
        获取sql解析，select、where、order、 group
        :param sql:
        :return:
        """
        pass

    # endregion


    @staticmethod
    def add_where_str(sql: str, condition: str) -> str:
        # 处理非字符串类型的边界情况
        if not isinstance(condition, str) or not condition.strip():
            return sql

        # 先清理原始 SQL 的重复关键字
        sql = SqlParseTool.clean_redundant_keywords(sql)
        swd = SqlParseTool.get_swo(sql)

        # 若原始 WHERE 含 OR，自动加括号
        original_where = swd['w'].strip()
        if original_where:
            if 'OR' in original_where.upper():
                original_where = f"({original_where})"
            new_where = f"WHERE {original_where} AND {condition}"
        else:
            new_where = f"WHERE {condition}"

        # 按标准顺序组装，保留原始格式（去掉标准化空格的逻辑）
        # （需修改 get_swo 不破坏原始格式，仅拆分关键字位置）
        parts = [swd['s'], new_where]
        if swd['g']:
            parts.append(swd['g'])
        if swd['h']:
            parts.append(swd['h'])
        if swd['o']:
            parts.append(swd['o'])

        # 用换行连接关键子句，保留基础可读性
        final_sql = '\n'.join(part.strip() for part in parts if part.strip())
        return final_sql

    @staticmethod
    def get_sql(base_sql: str, filter_value: str) -> str:
        """
        根据基础SQL和过滤值构建完整的查询SQL语句。

        自动检测SQL中是否使用了表别名(a.)，并相应调整WHERE条件字段。

        参数:
            base_sql (str): 基础SQL查询语句
            filter_value (str): 用于过滤的FNumber值

        返回:
            str: 构建完成的完整SQL语句

        示例:
            get_sql("SELECT * FROM table", "123")
            "SELECT * FROM table where FNumber='123'"

            get_sql("SELECT a.* FROM table a", "456")
            "SELECT a.* FROM table a where a.FNumber='456'"

        异常:
            ValueError: 如果filter_value为空或无效
        """
        if not filter_value or not isinstance(filter_value, str):
            raise ValueError("filter_value必须是非空字符串")

        # 解析SQL语句结构
        swd = SqlParseTool.get_swo(base_sql)
        select_part = swd['s']

        # 检查是否使用了表别名(a.)
        has_table_alias = re.search(r'\ba\.\w{2,}\b', select_part) is not None

        # 构建WHERE条件
        field_prefix = "a." if has_table_alias else ""
        where_condition = f" where {field_prefix} FNumber={quote(filter_value)}"

        # 组合完整SQL
        full_sql = select_part + where_condition

        return full_sql
